"""
读取配置文件中的所有证券计算他们的最大回撤
"""
import pandas as pd

from metainfo import conn


def calculate_drawdown(prices):
    peak = prices.cummax()
    drawdown = (prices - peak) / peak
    return drawdown


class IndexBasicInfo:

    def __init__(self, strategy, secuabbr, secucode, innercode, source_table, fields, date_fields):
        self.strategy = strategy
        self.secuabbr = secuabbr
        self.secucode = secucode
        self.innercode = innercode
        self.source_table = source_table
        self.fields = fields
        self.date_fields = date_fields

    def __setattr__(self, key, value):
        self.__dict__[key] = value


def get_source_info():
    query_sql = '''
                            select * from findfit_stock.major_assets_config

                      '''
    df = pd.read_sql_query(query_sql, conn)
    return df


def get_price_from_db(items):
    query_sql_list = []
    for item in items:
        secucode = item.secucode
        innercode = item.innercode
        source_table = item.source_table
        fields = item.fields
        date_fields = item.date_fields
        secuabbr = item.secuabbr
        query_sql = '''
                             select '{secucode}' as secucode,'{secuabbr}' as secuabbr, {enddate} as enddate , {fields} as closeprice  from {source_table} where innercode='{innercode}'
                            
                       '''
        sql = query_sql.format(secucode=secucode, innercode=innercode, source_table=source_table, fields=fields,
                               enddate=date_fields, secuabbr=secuabbr)
        query_sql_list.append(sql)
    df = pd.read_sql_query('union all'.join(query_sql_list), conn)
    return df


def get_price_from_csv():
    pass


def get_price_from_rpc():
    pass


strategy_map = {
    'db': get_price_from_db,
    'csv': get_price_from_csv,
    'rpc': get_price_from_rpc,
}

SECU_ABBR: str = 'secuabbr'
SECU_CODE: str = 'secucode'
INNERCODE: str = 'innercode'
STRATEGY: str = 'datasource'
SOURCE_TABLE: str = 'sourcetable'
CLOSE_PRICE: str = 'closefieldname'
ENDDATE: str = 'enddatefieldname'


def gen_item(row):
    # print('84:', row)
    strategy = row[STRATEGY]
    innercode = int(row[INNERCODE])
    table = row[SOURCE_TABLE]
    fields = row[CLOSE_PRICE]
    secucode = row[SECU_CODE]
    secuAbbr = row[SECU_ABBR]
    date_fields = row[ENDDATE]
    # print(secuAbbr)
    item = IndexBasicInfo(strategy, secuAbbr, secucode, innercode, table, fields, date_fields)
    return item


def get_index_close_price(row_list):
    """
       secucode,   enddate, closeprice

       """
    try:
        item_list = []
        strategy = row_list[0][STRATEGY].values[0]
        # print('100:', row_list)
        for row in row_list:
            item = gen_item(row.iloc[0].to_dict())
            item_list.append(item)
        df = strategy_map[strategy](item_list)
        return df

    except Exception as e:
        print('107:', e)


def read_file(dir):
    excel_file = pd.ExcelFile(dir)
    df = excel_file.parse('列表')
    return df


def save_file(df):
    merged_df = pd.concat(df, ignore_index=True)
    sort_df = merged_df.sort_values(by='enddate')
    sort_df['enddate'] = sort_df['enddate'].dt.strftime('%Y-%m-%d')
    sort_df.to_excel('sort_price.xlsx', index=False)

    # pivot_df = sort_df.pivot(index='enddate', columns='secucode', values='drawdown')
    # pivot_df.to_excel('pivot_price.xlsx')


def fetch_all():
    """
    1.读取配置文件
    2、顺序对每个指数查询数据
    3、将数据合并起来


    4、计算最大回撤
    secucode, innercode, enddate, closeprice, maxdropdown
    000300.SH              1日                   30%
    98000.SH              1日                   30%


    enddate, 000300.SH ,98000.SH ,secuCodeC
    1日        30%        20%       10%
    """
    data = read_file('./大类资产相关性.xlsx')
    dataframes = []
    for index, row in data.iterrows():
        df = get_index_close_price(row)
        # if df is not None:
        #     df['drawdown'] = calculate_drawdown(df['closeprice']).apply(lambda x: f'{x * 100:.2f}%')
        #     df['secucode'] = row[SECU_CODE]
        #     dataframes.append(df)
        dataframes.append(df)

    save_file(dataframes)


def classfiy_secucodes(secucodes):
    class_dict = {}
    df = get_source_info()
    for secucode in secucodes:
        if df[df['secucode'] == secucode].empty:
            continue
        source_table = df[df['secucode'] == secucode]['sourcetable'].values[0]
        if source_table not in class_dict:
            class_dict[source_table] = [df[df['secucode'] == secucode]]
        else:
            class_dict[source_table].append(df[df['secucode'] == secucode])
    return class_dict


def fetch_index_price(secucodes):
    """
      return :
      secucode,  enddate, closeprice

    """
    class_dict = classfiy_secucodes(secucodes)
    dataframes = []
    for single_class in class_dict:
        df = get_index_close_price(class_dict[single_class])
        # if df is not None:
        #     df['drawdown'] = calculate_drawdown(df['closeprice']).apply(lambda x: f'{x * 100:.2f}%')
        # df['secucode'] = df['secucode'].apply(lambda x: secucode_map[x] )
        #     dataframes.append(df)
        if df is not None:
            dataframes.append(df)
    return pd.concat(dataframes)


if __name__ == '__main__':
    #expand_cycle_data(["000906.SH"])
    secucodes=["000906.SH","930903.CSI"]
    df= fetch_index_price(secucodes)
    df['enddate'] = pd.to_datetime(df['enddate'])
    # 按 secucode 分组，然后在每个组内按月分组并取最后一条记录
    result = df.groupby('secucode').apply(lambda x: x.groupby(x['enddate'].dt.to_period('M')).last())
    # 重置索引
    result = result.reset_index(drop=True)
    print(result)
